#include "sqlhelper.h"
//#define DEBUG_MODE

SqlHelper& SqlHelper::instance()
{
    static SqlHelper inst;
    return inst;
}

SqlHelper::SqlHelper()
{
    assert(createConnection() && createTables());
}
SqlHelper::~SqlHelper()
{
#ifdef DEBUG_MODE
    deleteDataBase();
#endif
}

bool SqlHelper::createConnection()
{
    _db = QSqlDatabase::addDatabase("QSQLITE");
    _db.setDatabaseName("coffee.db");
    if (!_db.open()) {
        qDebug() << "Cannot open database:" << _db.lastError();
        return false;
    } else {
        qDebug() << "Create database succ";
        return true;
    }
}
bool SqlHelper::createTables()
{
    QString createUsersTable = R"(
        CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            password TEXT NOT NULL,
            type TEXT NOT NULL
        )
    )";
    QString createProductsTable = R"(
        CREATE TABLE IF NOT EXISTS product (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            brand TEXT NOT NULL,
            product TEXT NOT NULL,
            caffeine REAL NOT NULL
        )
    )";
    QString createCheckinTable = R"(
        CREATE TABLE IF NOT EXISTS record (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            timestamp TEXT NOT NULL,
            caffeine REAL NOT NULL
        )
    )";

    QSqlQuery query;
    if (query.exec(createUsersTable)
     && query.exec(createProductsTable)
     && query.exec(createCheckinTable)) {
        qDebug() << "table created successfully.";
        return true;
    } else {
        qDebug() << "Cannot create table:" << query.lastError();
        return false;
    }
}

void SqlHelper::deleteDataBase()
{
    if (_db.isValid()) {
        _db.close();
        QSqlDatabase::removeDatabase("coffee.db");
    }
    if (QFile::exists("coffee.db")) {
            if (QFile::remove("coffee.db")) {
                qDebug() << "Database file deleted successfully.";
            } else {
                qDebug() << "Failed to delete database file.";
            }
        } else {
            qDebug() << "Database file does not exist.";
    }
}

bool SqlHelper::insertUser(const QString &name, const QString &password, const QString &type)
{
    QSqlQuery query;
    query.prepare("INSERT INTO user (name, password, type) VALUES (:name, :password, :type)");
    query.bindValue(":name", name);
    query.bindValue(":password", password);
    query.bindValue(":type", type);
    if (!query.exec()) {
        qDebug() << "Error inserting into user table:" << query.lastError();
        return false;
    } else {
        qDebug() << "Inserted into user table successfully.";
        return true;
    }
}
bool SqlHelper::insertProduct(const QString &brand, const QString &product, double caffeine)
{
    QSqlQuery query;
    query.prepare("INSERT INTO product (brand, product, caffeine) VALUES (:brand, :product, :caffeine)");
    query.bindValue(":brand", brand);
    query.bindValue(":product", product);
    query.bindValue(":caffeine", caffeine);
    if (!query.exec()) {
        qDebug() << "Error inserting into product table:" << query.lastError();
        return false;
    } else {
        qDebug() << "Inserted into product table successfully.";
        return true;
    }
}

bool SqlHelper::insertRecord(const QString& name, const QString &timestamp, double caffeine)
{
    QSqlQuery query;
    query.prepare("INSERT INTO record (name, timestamp, caffeine) "
                  "VALUES (:name, :timestamp, :caffeine)");
    query.bindValue(":name", name);
    query.bindValue(":timestamp", timestamp);
    query.bindValue(":caffeine", caffeine);
    if (!query.exec()) {
        qDebug() << "Error inserting record:" << query.lastError();
        return false;
    } else {
        qDebug() << "Inserted record successfully.";
        return true;
    }
}

QString SqlHelper::getPassword(const QString &name)
{
    QSqlQuery query;
    query.prepare("SELECT password, type FROM user WHERE name = :name");
    query.bindValue(":name", name);
    if (!query.exec()) {
        qDebug() << "Error retrieving user info:" << query.lastError();
        return QString();
    }
    if (query.next()) {
        QString password = query.value(0).toString();
        return password;
    } else {
        qDebug() << "No user found with the name:" << name;
        return QString();
    }
}
QString SqlHelper::getType(const QString &name)
{
    QSqlQuery query;
    query.prepare("SELECT type, type FROM user WHERE name = :name");
    query.bindValue(":name", name);
    if (!query.exec()) {
        qDebug() << "Error retrieving user info:" << query.lastError();
        return QString();
    }
    if (query.next()) {
        QString type = query.value(0).toString();
        return type;
    } else {
        qDebug() << "No user found with the name:" << name;
        return QString();
    }
}
double SqlHelper::getCaffeine(const QString &brand, const QString &product)
{
    QSqlQuery query;
    query.prepare("SELECT caffeine FROM product WHERE brand = :brand AND product = :product");
    query.bindValue(":brand", brand);
    query.bindValue(":product", product);
    if (!query.exec()) {
        qDebug() << "Error retrieving product info:" << query.lastError();
        return -1.0;
    }
    if (query.next()) {
        return query.value(0).toDouble();
    } else {
        qDebug() << "No product found with the given brand and product name:" << brand << product;
        return -1.0;
    }
}

